﻿-- Function: nearest_stops_detail(text, text)

-- DROP FUNCTION nearest_stops_detail(text, text);

CREATE OR REPLACE FUNCTION nearest_stops_detail(latitude text, longitude text)
  RETURNS SETOF my_type AS
$BODY$
DECLARE sql text;
DECLARE r my_type;
BEGIN

	DROP TABLE IF EXISTS tmp_nearest_stops;
	DROP TABLE IF EXISTS tmp_destination;

sql := 	'CREATE TEMPORARY TABLE tmp_nearest_stops AS ' || 
	'SELECT a.*,b.route,b.run,b.sequence, b.sequence as destinationsequence,b.stop_name as destination_stop_name ' ||
	'FROM nearest_stops(''' || latitude || ''',''' || longitude || ''') a, londonbusstopsequence b ' ||
	'WHERE a.bus_stop_code = b.bus_stop_code';

	EXECUTE sql;

	CREATE TEMPORARY TABLE tmp_destination
	AS SELECT max(to_number(s.sequence,'999')) as sequence,s.route,s.run
	FROM londonbusstopsequence s,tmp_nearest_stops t
	WHERE s.route = t.route
	AND   s.run = t.run
	GROUP BY s.route,s.run;

	UPDATE tmp_nearest_stops ts
	SET destinationsequence = d.sequence
	FROM tmp_destination d
	WHERE d.route = ts.route
	AND d.run = ts.run;

	UPDATE tmp_nearest_stops ts
	SET destination_stop_name = s.stop_name
	FROM londonbusstopsequence s
	WHERE ts.destinationsequence = s.sequence
	AND ts.route = s.route
	AND ts.run = s.run; 

	sql:= 'SELECT route,bus_stop_code,stop_name,destination_stop_name as destination FROM tmp_nearest_stops ' ||
	      'WHERE destinationsequence != sequence ';
	

	FOR r IN EXECUTE(sql) LOOP
		RETURN NEXT r;
	END LOOP;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION nearest_stops_detail(text, text) OWNER TO postgres;
